unit clsNFOperacoes;

interface

uses uDMCI, DbTables, ubiblioCI, sysutils, DB;

type
  TCLNFOperacoes = class(TObject)
  private
    CodF_codigo: string;
  public
    NFOperacoesDESCRICAO,
    NFOperacoesCFOP,
    NFOperacoesTIPONF,
    NFOperacoesNFOPER_LIVRE_PEDIDO,
    NFOperacoesNFOPER_LIVRE_ESTOQUE,
    NFOperacoesNFOPER_OBS_FISCAL: String;
    NFOperacoesID_OPERACAO,
    NFOperacoesNFOPER_ICMS_TIPOTRIB,
    NFOperacoesNFOPER_IPI_TIPOTRIB,
    NFOperacoesNFOPER_PIS_TIPOTRIB,
    NFOperacoesNFOPER_COFINS_TIPOTRIB,
    NFOperacoesNFOPER_ISSQN_TIPOTRIB,
    NFOperacoesNFOPER_ICMS_FIXO_ID,
    NFOperacoesNFOPER_IPI_FIXO_ID,
    NFOperacoesNFOPER_PIS_FIXO_ID,
    NFOperacoesNFOPER_COFINS_FIXO_ID,
    NFOperacoesNFOPER_ISSQN_FIXO_ID : Double;

    ID_OPERACAO,
    ID_TRIBUTACAO,
    ID_NCM : Integer;
    TIPO_IMPOSTO,
    CFOP,
    SIGLA_ESTADO : String;

    aEmpresas : String;

    function Gravar(P_ID_OPERACAO: Integer): Byte;
    function Excluir(P_ID_OPERACAO: Integer): Byte;
    procedure Consultar(var p_q: TQuery);
    function RetornaMaiorCodigo: Integer;
    procedure Consultar2(var p_q: TQuery; P_DESCRICAO: string);
    procedure ConsultarEmpresa(var p_q: TQuery);
    procedure RecuperarEmpresa(var p_q: TQuery);
    procedure CopiaConsultar(var p_q: TQuery);
    procedure CopiaConsultarNCM(var p_q: TQuery);
    function CopiaGravar(P_ID_OPERACAO: Integer; P_TP_IMPOSTO: String): Byte;
    procedure CopiaConsultarNCMUF(var p_q: TQuery);
    function CopiaGravarUF(P_ID_OPERACAO: Integer; P_TP_IMPOSTO: String): Byte;
    function ExcluirUF(P_ID_OPERACAO: Integer; P_TP_IMPOSTO: String): Byte;
  end;

implementation

{ TCodFiscal }

procedure TCLNFOperacoes.Consultar(var p_q: TQuery);
begin
  p_q.sql.Clear;
  p_q.Params.Clear;
  p_q.sql.add(' SELECT * FROM NF_OPERACOES ORDER BY ID_OPERACAO ');
end;

procedure TCLNFOperacoes.RecuperarEmpresa(var p_q: TQuery);
begin
  p_q.sql.Clear;
  p_q.Params.Clear;
  p_q.sql.add(' SELECT * FROM NFOPERACOES_EMPRESA ');
end;

procedure TCLNFOperacoes.ConsultarEmpresa(var p_q: TQuery);
begin
  p_q.sql.Clear;
  p_q.Params.Clear;
  p_q.sql.add(' SELECT * FROM EMPRESA ');
end;

procedure TCLNFOperacoes.Consultar2(var p_q: TQuery; P_DESCRICAO: string);
begin
  p_q.sql.Clear;
  p_q.Params.Clear;
  p_q.sql.add('SELECT * FROM NF_OPERACOES WHERE DESCRICAO LIKE (:P_DESCRICAO) ORDER BY DESCRICAO');
  p_q.parambyname('P_DESCRICAO').asstring := P_DESCRICAO;
end;

function TCLNFOperacoes.Excluir(P_ID_OPERACAO: Integer): Byte;
var
  tq: TQuery;
begin
  tq := Tquery.Create(DMCI);
  tq.databasename := DMCI.dbCentralImpostos.databasename;
  tq.active := False;
  tq.close;
  tq.sql.Clear;
  tq.params.clear;
  tq.sql.add('DELETE FROM NF_OPERACOES WHERE ID_OPERACAO= :P_ID_OPERACAO');
  tq.parambyname('P_ID_OPERACAO').AsInteger := P_ID_OPERACAO;
  tq.execSQL;
  Result := 1;
  tq.close;
  tq.Free;
end;


function TCLNFOperacoes.ExcluirUF(P_ID_OPERACAO: Integer; P_TP_IMPOSTO: String): Byte;
var
  tq: TQuery;
begin
  tq := Tquery.Create(DMCI);
  tq.databasename := DMCI.dbCentralImpostos.databasename;
  tq.active := False;
  tq.close;
  tq.sql.Clear;
  tq.params.clear;
  tq.sql.add(' DELETE NF_OPERACOES_NCMPORESTADO WHERE NFNCMEST_ID_OPERACAO = :ID_OPERACAO AND NFNCMEST_TIPO_IMPOSTO = :TPIMPOSTO ');
  tq.parambyname('ID_OPERACAO').AsInteger := P_ID_OPERACAO;
  tq.parambyname('TPIMPOSTO').AsString    := P_TP_IMPOSTO;
  tq.execSQL;
  Result := 1;
  tq.close;
  tq.Free;
end;


function TCLNFOperacoes.CopiaGravar(P_ID_OPERACAO: Integer; P_TP_IMPOSTO: String): Byte;
var
  tq, tq1: TQuery;
Posicao, I : Integer;
begin
  tq := Tquery.Create(DMCI);
  tq.databasename := DMCI.dbCentralImpostos.databasename;
  tq.active := False;
  tq.sql.clear;
  tq.params.clear;

  tq1              := Tquery.Create(DMCI);
  tq1.databasename := DMCI.dbCentralImpostos.databasename;
  tq1.active       := False;
  tq1.sql.clear;
  tq1.params.clear;

  tq.sql.add(' SELECT * FROM NF_OPERACOES_PORNCM WHERE NFNCM_ID_OPERACAO = :ID_OPERACAO AND NFNCM_TIPO_IMPOSTO = :TPIMPOSTO ');
  tq.parambyname('ID_OPERACAO').AsInteger := P_ID_OPERACAO;
  tq.parambyname('TPIMPOSTO').AsString    := P_TP_IMPOSTO;
  tq.open;
  if tq.eof then begin
    tq1.close;
    tq1.sql.clear;
    tq1.params.clear;
    tq1.sql.add(' INSERT INTO NF_OPERACOES_PORNCM (NFNCM_ID_OPERACAO,NFNCM_TIPO_IMPOSTO,NFNCM_ID_TRIBUTACAO,NFNCM_ID_NCM,NFNCM_CFOP) ');
    tq1.sql.add(' VALUES(:P_NFNCM_ID_OPERACAO,:P_NFNCM_TIPO_IMPOSTO,:P_NFNCM_ID_TRIBUTACAO,:P_NFNCM_ID_NCM,:P_NFNCM_CFOP) ');
  end else begin
    tq1.close;
    tq1.sql.clear;
    tq1.params.clear;
    tq1.sql.add(' UPDATE NF_OPERACOES_PORNCM SET NFNCM_ID_TRIBUTACAO = :P_NFNCM_ID_TRIBUTACAO, NFNCM_CFOP = :P_NFNCM_CFOP ');
    tq1.sql.add(' WHERE NFNCM_ID_OPERACAO = :P_NFNCM_ID_OPERACAO AND NFNCM_TIPO_IMPOSTO = :P_NFNCM_TIPO_IMPOSTO AND NFNCM_ID_NCM = :P_NFNCM_ID_NCM ');
  end;

  tq1.parambyname('P_NFNCM_ID_OPERACAO').AsInteger   := ID_OPERACAO;
  tq1.parambyname('P_NFNCM_TIPO_IMPOSTO').AsString   := TIPO_IMPOSTO;
  tq1.parambyname('P_NFNCM_ID_TRIBUTACAO').AsInteger := ID_TRIBUTACAO;
  tq1.parambyname('P_NFNCM_ID_NCM').AsInteger        := ID_NCM;
  tq1.parambyname('P_NFNCM_CFOP').AsString           := CFOP;
  tq1.execsql;
  tq1.close;
  result := 1;
  tq.close;
  tq1.Free;
  tq.Free;
end;


function TCLNFOperacoes.CopiaGravarUF(P_ID_OPERACAO: Integer; P_TP_IMPOSTO: String): Byte;
var
  tq1: TQuery;
  Posicao, I : Integer;
begin
  tq1              := Tquery.Create(DMCI);
  tq1.databasename := DMCI.dbCentralImpostos.databasename;
  tq1.active       := False;
  tq1.sql.clear;
  tq1.params.clear;

  tq1.close;
  tq1.sql.clear;
  tq1.params.clear;
  tq1.sql.add(' INSERT INTO NF_OPERACOES_NCMPORESTADO (NFNCMEST_ID_OPERACAO,NFNCMEST_ID_NCM,NFNCMEST_ID_IMPOSTO,NFNCMEST_TIPO_IMPOSTO, ' +
  '             NFNCMEST_SIGLA_ESTADO,NFNCMEST_CFOP) ');
  tq1.sql.add(' VALUES(:P_NFNCMEST_ID_OPERACAO,:P_NFNCMEST_ID_NCM,:P_NFNCMEST_ID_IMPOSTO,:P_NFNCMEST_TIPO_IMPOSTO, '+
  '             :P_NFNCMEST_SIGLA_ESTADO, :P_NFNCMEST_CFOP) ');


  tq1.parambyname('P_NFNCMEST_ID_OPERACAO').AsInteger := ID_OPERACAO;
  tq1.parambyname('P_NFNCMEST_ID_NCM').AsInteger      := ID_NCM;
  tq1.parambyname('P_NFNCMEST_ID_IMPOSTO').AsInteger  := ID_TRIBUTACAO;
  tq1.parambyname('P_NFNCMEST_TIPO_IMPOSTO').AsString := TIPO_IMPOSTO;
  tq1.parambyname('P_NFNCMEST_SIGLA_ESTADO').AsString := SIGLA_ESTADO;
  tq1.parambyname('P_NFNCMEST_CFOP').AsString         := CFOP;
  tq1.execsql;
  tq1.close;
  result := 1;
  tq1.Free;
end;


function TCLNFOperacoes.Gravar(P_ID_OPERACAO: Integer): Byte;
var
  tq, tq1: TQuery;
Posicao, I : Integer;
begin
  tq := Tquery.Create(DMCI);
  tq.databasename := DMCI.dbCentralImpostos.databasename;
  tq.active := False;
  tq.sql.clear;
  tq.params.clear;

  tq1              := Tquery.Create(DMCI);
  tq1.databasename := DMCI.dbCentralImpostos.databasename;
  tq1.active       := False;
  tq1.sql.clear;
  tq1.params.clear;

  tq.sql.add(' SELECT * FROM NF_OPERACOES WHERE ID_OPERACAO= :P_ID_OPERACAO ');
  tq.parambyname('P_ID_OPERACAO').AsInteger := P_ID_OPERACAO;
  tq.open;
  if tq.eof then begin
    tq1.close;
    tq1.sql.clear;
    tq1.params.clear;
    tq1.sql.add(' INSERT INTO NF_OPERACOES (DESCRICAO,CFOP,TIPONF, ');
    tq1.sql.add('              NFOPER_OBS_FISCAL,NFOPER_LIVRE_PEDIDO, ');
    tq1.sql.add('              NFOPER_LIVRE_ESTOQUE,ID_OPERACAO,NFOPER_ICMS_TIPOTRIB,NFOPER_IPI_TIPOTRIB, ');
    tq1.sql.add('              NFOPER_PIS_TIPOTRIB,NFOPER_COFINS_TIPOTRIB,NFOPER_ISSQN_TIPOTRIB,NFOPER_ICMS_FIXO_ID, ');
    tq1.sql.add('              NFOPER_IPI_FIXO_ID,NFOPER_PIS_FIXO_ID,nfoper_cofins_fixo_id,nfoper_issqn_fixo_id) ');
    tq1.sql.add(' VALUES(:NFOPeracoesDESCRICAO,:NFOPeracoesCFOP,:NFOPeracoesTIPONF,:NFOPeracoesNFOPEROBSFISC,:NFOPeracoesNFOPERLIVPED, ');
    tq1.sql.add('              :NFOPeracoesNFOPERLIVREEST,:NFOPeracoesID_OPERACAO,:NFOPeracoesNFOPERICMSTPTRIB,:NFOPeracoesNFOPERIPITPTRIB, ');
    tq1.sql.add('              :NFOPeracoesNFOPERPISTPTRIB,:NFOPeracoesNFOPERCOFINSTPTRIB,:NFOPeracoesNFOPERISSQNTPTRIB,:NFOPeracoesNFOPERICMSFIXOID, ');
    tq1.sql.add('              :NFOPeracoesNFOPER_IPI_FIXO_ID,:NFOPeracoesNFOPERPISFIXOID,:NFOPeracoesNFOPERCOFINSFXID,:NFOPeracoesNFOPERISSQNFXID) ');

  end else begin
    tq1.close;
    tq1.sql.clear;
    tq1.params.clear;
    tq1.sql.add(' UPDATE NF_OPERACOES SET DESCRICAO = :NFOPeracoesDESCRICAO,CFOP = :NFOPeracoesCFOP,TIPONF = :NFOPeracoesTIPONF, ');
    tq1.sql.add('        NFOPER_OBS_FISCAL = :NFOPeracoesNFOPEROBSFISC, NFOPER_LIVRE_PEDIDO = :NFOPeracoesNFOPERLIVPED, ');
    tq1.sql.add('        NFOPER_LIVRE_ESTOQUE = :NFOPeracoesNFOPERLIVREEST,NFOPER_ICMS_TIPOTRIB = :NFOPeracoesNFOPERICMSTPTRIB, ');
    tq1.sql.add('        NFOPER_IPI_TIPOTRIB = :NFOPeracoesNFOPERIPITPTRIB, NFOPER_PIS_TIPOTRIB = :NFOPeracoesNFOPERPISTPTRIB,NFOPER_COFINS_TIPOTRIB = :NFOPeracoesNFOPERCOFINSTPTRIB, ');
    tq1.sql.add('        NFOPER_ISSQN_TIPOTRIB = :NFOPeracoesNFOPERISSQNTPTRIB,NFOPER_ICMS_FIXO_ID = :NFOPeracoesNFOPERICMSFIXOID, ');
    tq1.sql.add('        NFOPER_IPI_FIXO_ID = :NFOPeracoesNFOPER_IPI_FIXO_ID,NFOPER_PIS_FIXO_ID = :NFOPeracoesNFOPERPISFIXOID, ');
    tq1.sql.add('        nfoper_cofins_fixo_id = :NFOPeracoesNFOPERCOFINSFXID,nfoper_issqn_fixo_id = :NFOPeracoesNFOPERISSQNFXID ');
    tq1.sql.add('   WHERE ID_OPERACAO = :NFOPeracoesID_OPERACAO ');
  end;

  tq1.parambyname('NFOPeracoesDESCRICAO').AsString           := NFOPeracoesDESCRICAO;
  tq1.parambyname('NFOPeracoesCFOP').AsString                := NFOPeracoesCFOP;
  tq1.parambyname('NFOPeracoesTIPONF').AsString              := NFOPeracoesTIPONF;
  tq1.parambyname('NFOPeracoesNFOPEROBSFISC').AsString       := NFOPeracoesNFOPER_OBS_FISCAL;
  tq1.parambyname('NFOPeracoesNFOPERLIVPED').AsString        := NFOPeracoesNFOPER_LIVRE_PEDIDO;
  tq1.parambyname('NFOPeracoesNFOPERLIVREEST').AsString      := NFOPeracoesNFOPER_LIVRE_ESTOQUE;
  tq1.parambyname('NFOPeracoesID_OPERACAO').AsFloat          := P_ID_OPERACAO;
  tq1.parambyname('NFOPeracoesNFOPERICMSTPTRIB').AsFloat     := NFOPeracoesNFOPER_ICMS_TIPOTRIB;
  tq1.parambyname('NFOPeracoesNFOPERIPITPTRIB').AsFloat      := NFOPeracoesNFOPER_IPI_TIPOTRIB;
  tq1.parambyname('NFOPeracoesNFOPERPISTPTRIB').AsFloat      := NFOPeracoesNFOPER_PIS_TIPOTRIB;
  tq1.parambyname('NFOPeracoesNFOPERCOFINSTPTRIB').AsFloat   := NFOPeracoesNFOPER_COFINS_TIPOTRIB;
  tq1.parambyname('NFOPeracoesNFOPERISSQNTPTRIB').AsFloat    := NFOPeracoesNFOPER_ISSQN_TIPOTRIB;
  tq1.parambyname('NFOPeracoesNFOPERICMSFIXOID').AsFloat     := NFOPeracoesNFOPER_ICMS_FIXO_ID;
  tq1.parambyname('NFOPeracoesNFOPER_IPI_FIXO_ID').AsFloat   := NFOPeracoesNFOPER_IPI_FIXO_ID;
  tq1.parambyname('NFOPeracoesNFOPERPISFIXOID').AsFloat      := NFOperacoesNFOPER_PIS_FIXO_ID;
  tq1.parambyname('NFOPeracoesNFOPERCOFINSFXID').AsFloat     := NFOperacoesNFOPER_COFINS_FIXO_ID;
  tq1.parambyname('NFOPeracoesNFOPERISSQNFXID').AsFloat      := NFOPeracoesNFOPER_ISSQN_FIXO_ID;
  tq1.execsql;
  tq1.close;
  result := 1;
  tq.close;

  Posicao := 1;

  tq.close;
  tq.SQL.Clear;
  tq.Params.Clear;
  tq.sql.add(' DELETE FROM NFOPERACOES_EMPRESA WHERE ID_OPERACAO = :PID_OPERACAO');
  tq.ParamByName('PID_OPERACAO').AsInteger := P_ID_OPERACAO;
  tq.ExecSQL;
  if Length(aEmpresas) = 3 then begin
    tq1.close;
    tq1.sql.clear;
    tq1.params.clear;
    tq1.SQL.Add('INSERT INTO NFOPERACOES_EMPRESA(ID_OPERACAO, EMP_CODIGO) VALUES (:P_IDOPERACAO, :P_EMPRESA)');
    tq1.ParamByName('P_IDOPERACAO').AsInteger := P_ID_OPERACAO;
    tq1.ParamByName('P_EMPRESA').AsString     := copy(aEmpresas,Posicao,3);
    tq1.ExecSQL;
  end else begin
    for I := 0 to Length(aEmpresas) - 4 do begin
      if copy(aEmpresas,Posicao,3) <> '' then begin
        try
          tq1.close;
          tq1.sql.clear;
          tq1.params.clear;
          tq1.SQL.Add('INSERT INTO NFOPERACOES_EMPRESA(ID_OPERACAO, EMP_CODIGO) VALUES (:P_IDOPERACAO, :P_EMPRESA)');
          tq1.ParamByName('P_IDOPERACAO').AsInteger := P_ID_OPERACAO;
          tq1.ParamByName('P_EMPRESA').AsString     := copy(aEmpresas,Posicao,3);
          tq1.ExecSQL;
        except

        end;
      end;
      Posicao := Posicao + 4;
    end;
  end;
  tq1.Free;
  tq.Free;
end;


procedure TCLNFOperacoes.CopiaConsultar(var p_q: TQuery);
begin
  p_q.sql.Clear;
  p_q.Params.Clear;
  p_q.sql.add(' SELECT N  .NFOPER_ICMS_TIPOTRIB, N.NFOPER_IPI_TIPOTRIB, N.NFOPER_PIS_TIPOTRIB, N.NFOPER_COFINS_TIPOTRIB, N.NFOPER_ISSQN_TIPOTRIB, '+
              '        N.NFOPER_ICMS_FIXO_ID, N.NFOPER_IPI_FIXO_ID, N.NFOPER_PIS_FIXO_ID, N.NFOPER_COFINS_FIXO_ID, N.NFOPER_ISSQN_FIXO_ID '+
              ' FROM NF_OPERACOES N WHERE N.ID_OPERACAO = :ID_OPERACAO ');
end;

procedure TCLNFOperacoes.CopiaConsultarNCM(var p_q: TQuery);
begin
  p_q.sql.Clear;
  p_q.Params.Clear;
  p_q.sql.add(' SELECT * FROM NF_OPERACOES_PORNCM WHERE NFNCM_ID_OPERACAO = :ID_OPERACAO AND NFNCM_TIPO_IMPOSTO = :TPIMPOSTO ');
end;

procedure TCLNFOperacoes.CopiaConsultarNCMUF(var p_q: TQuery);
begin
  p_q.sql.Clear;
  p_q.Params.Clear;
  p_q.sql.add(' SELECT * FROM NF_OPERACOES_NCMPORESTADO WHERE NFNCMEST_ID_OPERACAO = :ID_OPERACAO AND NFNCMEST_TIPO_IMPOSTO = :TPIMPOSTO ');
end;

function TCLNFOperacoes.RetornaMaiorCodigo(): Integer;
var
  tq1: TQuery;
begin
  tq1 := Tquery.Create(DMCI);
  tq1.databasename := DMCI.dbCentralImpostos.databasename;
  tq1.active := False;
  tq1.close;
  tq1.sql.Clear;
  tq1.params.clear;
  tq1.sql.add(' SELECT MAX(ID_OPERACAO) AS ID_OPERACAO FROM NF_OPERACOES ');
  tq1.Open;
  if trim(tq1.FieldByName('ID_OPERACAO').AsString) = '' then
    Result := 0
  else
    Result := tq1.FieldByName('ID_OPERACAO').AsInteger;
  tq1.close;
  tq1.Free;
end;

end.





